package com.cyf.db.mapper;

import com.cyf.db.bean.InOutParking;
import com.cyf.wechat.network.bean.InOutParkingRsp;
import org.apache.ibatis.annotations.*;

import java.util.List;


public interface InOutParkingMapper {

	@Select("select carLicense, primaryInTime, primaryOutTime, p.name parkingName " +
			"from local_inoutparking i, parking p where i.parkingId = p.id " +
			"and i.primaryOutTime >= #{primaryInTime} and i.primaryOutTime <= #{primaryOutTime} " +
			"and carLicense in (#{plate1},#{plate2},#{plate3}) " +
			"order by i.primaryOutTime desc " +
			"limit ${start}, ${end}")
	List<InOutParking> getByTimeAndPlate(@Param("primaryInTime") String primaryInTime, @Param("primaryOutTime") String primaryOutTime,
                                         @Param("plate1") String plate1, @Param("plate2") String plate2, @Param("plate3") String plate3,
										 @Param("start") int start, @Param("end") int end);

	@Select("select i.id, carLicense, primaryInTime, primaryOutTime, p.name parkingName, parkingId, primaryStayTime, localInoutparkingId from local_inoutparking i , parking p " +
			"where i.parkingId = p.id and carLicense in (#{plate1},#{plate2},#{plate3}) order by i.primaryOutTime desc limit ${start}, ${end}")
	List<InOutParking> getByPlate(@Param("plate1") String plate1, @Param("plate2") String plate2, @Param("plate3") String plate3,
								  @Param("start") int start, @Param("end") int end);

	@Select("select count(*) from local_inoutparking i where ((i.primaryInTime >= #{primaryInTime} and i.primaryInTime <= #{primaryOutTime}) " +
			"or (i.primaryOutTime >= #{primaryInTime} and i.primaryOutTime <= #{primaryOutTime})) " +
			"and carLicense in (#{plate1},#{plate2},#{plate3})")
	int getCountByTimeAndPlate(@Param("primaryInTime") String primaryInTime, @Param("primaryOutTime") String primaryOutTime,
                               @Param("plate1") String plate1, @Param("plate2") String plate2, @Param("plate3") String plate3);

	@Select("select count(*) from local_inoutparking i where carLicense in (#{plate1},#{plate2},#{plate3})")
	int getCountByPlate(@Param("plate1") String plate1, @Param("plate2") String plate2, @Param("plate3") String plate3);

	@Select("select carLicense, primaryInTime, primaryOutTime, p.name parkingName,parkingId, i.localInoutparkingId,i.id " +
			"from local_inoutparking i, parking p where i.parkingId = p.id " +
			"and carLicense in (#{plate1},#{plate2},#{plate3}) " +
			"order by i.primaryOutTime desc " +
			"limit ${start}, ${end}")
	List<InOutParking> getByPlates(@Param("plate1") String plate1, @Param("plate2") String plate2, @Param("plate3") String plate3,
										 @Param("start") int start, @Param("end") int end);

	@Select("<script>" +
			"SELECT io.id,io.parkingId,io.primaryInTime AS inTime,io.primaryOutTime AS outTime," +
			"io.carLicense AS plate,io.primaryStayTime AS stopTime,p.`name` AS parkingName, " +
			"de.actualFee AS actualFee,de.payWay,de.couponFee,de.shouldFee,de.localFeegroupId as feeGroupId," +
			"io.localInoutparkingId,io.primaryFeeGroupName as  feeGroupName " +
			"FROM local_inoutparking as io " +
			"LEFT JOIN parking AS p ON io.parkingId = p.id  " +
			"LEFT JOIN payorder as de ON io.recordOrderId = de.recordOrderId AND io.parkingId = de.parkingId " +
			"WHERE io.carLicense = #{plate} and  de.payStatus = 1 "+
			"<if test=\"inTime != null and inTime != '' \">" +
			" AND io.primaryInTime BETWEEN #{inTime} AND #{outTime} " +
			"</if>" +
			"<if test=\"staMoney != null and staMoney != '' \">" +
			" AND de.actualFee BETWEEN #{staMoney} AND #{endMoney} " +
			"</if>" +
			"  GROUP BY io.id ORDER BY io.primaryOutTime desc " +
			"</script>")
	List<InOutParkingRsp> selectInOutParkingRecord(@Param("plate") String plate,@Param("inTime") String inTime,@Param("outTime") String outTime,@Param("staMoney") Integer staMoney,@Param("endMoney") Integer endMoney);




	@Select("<script>" +
		    " SELECT io.id,io.parkingId,io.primaryInTime AS inTime,io.primaryOutTime AS outTime,io.carLicense AS plate,io.primaryStayTime AS stopTime,p.`name` AS parkingName  \n" +
			" ,io.localInoutparkingId  FROM  \n" +
			"  local_inoutparking as io \n" +
			"  LEFT JOIN parking AS p ON io.parkingId = p.id   \n" +
			"  WHERE  io.carLicense in " +
			" <foreach item=\"item\" index=\"index\" collection=\"plates\" open=\"(\" separator=\",\" close=\")\">  \n" +
			"  #{item}  \n" +
			" </foreach>  " +
			" <if test=\"inTime != null and inTime != '' \">" +
			"  AND io.primaryOutTime BETWEEN #{inTime} AND #{outTime} " +
			" </if>" +
			"  GROUP BY io.id ORDER BY io.primaryOutTime desc"+
			"</script>")
	List<InOutParkingRsp> selectInOutParkingRecordOtherList(@Param("plates") List<String> plates,@Param("inTime") String inTime,@Param("outTime") String outTime);

	/*@Select("<script>"+
			" SELECT io.id,io.parkingId,io.primaryInTime AS inTime,io.primaryOutTime AS outTime,io.carLicense AS plate,io.primaryStayTime AS stopTime,p.`name` AS parkingName,  " +
			" de.actualFee AS actualFee,de.payWay,de.couponFee,de.shouldFee,de.localFeegroupId as feeGroupId,io.localInoutparkingId  FROM  " +
			" local_inoutparking as io  " +
			" LEFT JOIN parking AS p ON io.parkingId = p.id   " +
			" LEFT JOIN payorder as de ON io.recordOrderId = de.recordOrderId AND io.parkingId = de.parkingId  " +
			" WHERE io.carLicense in  " +
			" <foreach item=\"item\" index=\"index\" collection=\"plates\" open=\"(\" separator=\",\" close=\")\">  \n" +
			"  #{item}  \n" +
			" </foreach>  " +
			" <if test=\"inTime != null and inTime != '' \">" +
			"  AND io.primaryInTime BETWEEN #{inTime} AND #{outTime} " +
			" </if>" +
			" AND io.id NOT IN ( " +
			" SELECT io.id  FROM  " +
			"  local_inoutparking as io  " +
			"   LEFT JOIN parking AS p ON io.parkingId = p.id  " +
			"  LEFT JOIN piaotong_park as rk ON io.parkingId = rk.parkingId   " +
			"   LEFT JOIN piaotong_invoice AS ice ON ice.localInoutparkingId = io.localInoutparkingId AND ice.parkingId = io.parkingId  " +
			"   LEFT JOIN (SELECT (actualfee + wechatFee) AS actualfee,parkingId,inoutParkingId FROM local_feedetail ) AS c ON c.parkingId = io.parkingId AND inoutParkingId  = io.localInoutparkingId  " +
			"  WHERE  rk.id IS NOT null AND ice.id IS NULL AND c.actualfee !=0  AND io.carLicense in  " +
			" <foreach item=\"item\" index=\"index\" collection=\"plates\" open=\"(\" separator=\",\" close=\")\">  \n" +
			"  #{item}  \n" +
			" </foreach>  " +
			" <if test=\"inTime != null and inTime != '' \">" +
			"  AND io.primaryInTime BETWEEN #{inTime} AND #{outTime} " +
			" </if>" +
			" GROUP BY io.id ORDER BY io.primaryOutTime desc " +
			" ) " +
			" GROUP BY io.id ORDER BY io.primaryOutTime desc"+
			"</script>")
	List<InOutParkingRsp> selectInOutParkingRecordOtherList(@Param("plates") List<String> plates,@Param("inTime") String inTime,@Param("outTime") String outTime);
*/



	@Select("<script>" +
			" SELECT io.id,io.parkingId,io.primaryInTime AS inTime,io.primaryOutTime AS outTime," +
			" io.carLicense AS plate,io.primaryStayTime AS stopTime,p.`name` AS parkingName,io.recordOrderId  " +
			" ,io.localInoutparkingId FROM  " +
			"  local_inoutparking as io  " +
			"  LEFT JOIN parking AS p ON io.parkingId = p.id   " +
			"  LEFT JOIN piaotong_park as rk ON io.parkingId = rk.parkingId  " +
			"  LEFT JOIN piaotong_invoice AS ice ON find_in_set(io.localInoutparkingId, ice.localInoutparkingId) AND ice.parkingId = io.parkingId " +
			"  WHERE  rk.id IS NOT null AND ice.id IS NULL  AND io.carLicense in  " +
			" <foreach item=\"item\" index=\"index\" collection=\"plates\" open=\"(\" separator=\",\" close=\")\">  \n" +
			"\n" +
			"  #{item}  \n" +
			"\n" +
			" </foreach>  " +
			"<if test=\"inTime != null and inTime != '' \">" +
			" AND io.primaryOutTime BETWEEN #{inTime} AND #{outTime} " +
			"</if>" +
			"  GROUP BY io.id ORDER BY io.primaryOutTime desc " +
			"</script>")
	List<InOutParkingRsp> selectInOutParkingRecordList(@Param("plates") List<String> plates,@Param("inTime") String inTime,@Param("outTime") String outTime);

}
